﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class News
    {
        public bool GetNews(string id, out Util.News news)
        {
            SqlDataReader sdr;
            news = new Util.News();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT * FROM News WHERE NewsID = @ID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ID";
                param.Value = id;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        news = new Util.News { NewsID = sdr["NewsID"].ToString(), Title = sdr["Title"].ToString(), NewsBody = sdr["NewsBody"].ToString(), UserName = sdr["UserName"].ToString(), Date = sdr["Date"].ToString() };
                    }
                }
            }

            return true;
        }

        public bool GetNewsList(out List<Util.News> newsList)
        {
            SqlDataReader sdr;

            newsList = new List<Util.News>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT NewsID, Title FROM News";
                SqlCommand comm = new SqlCommand(strSQL, con);
                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        newsList.Add(new Util.News { Title = sdr["Title"].ToString(), NewsID = sdr["NewsID"].ToString() });
                    }
                }
            }

            return true;
        }

        public bool SendNews(Util.News news)
        {
            int count;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "INSERT INTO News (UserName, Title, NewsBody, Date) VALUES (@UserName, @Title, @newsBody, @date)";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@UserName";
                param.Value = news.UserName;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Title";
                param.Value = news.Title;
                param.SqlDbType = SqlDbType.Text;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Date";
                param.Value = news.Date;
                param.SqlDbType = SqlDbType.DateTime;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@NewsBody";
                param.Value = news.NewsBody;
                param.SqlDbType = SqlDbType.Text;
                comm.Parameters.Add(param);

                con.Open();

                count = comm.ExecuteNonQuery();
            }
            if (count != 0)
                return true;
            else
                return false;
        }

        public bool EditNews(string id, Util.News news)
        {
            int count;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "UPDATE News SET Title = @Title, NewsBody = @NewsBody WHERE NewsID = @NewsID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@NewsID";
                param.Value = news.NewsID;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@Title";
                param.Value = news.Title;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@NewsBody";
                param.Value = news.NewsBody;
                param.SqlDbType = SqlDbType.Text;
                comm.Parameters.Add(param);

                con.Open();

                count = comm.ExecuteNonQuery();
            }
            if (count != 0)
                return true;
            else
                return false;
        }

        public bool DeleteNews(string id)
        {
            int count;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "DELETE FROM News WHERE NewsID = @NewsID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@NewsID";
                param.Value = id;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                con.Open();

                count = comm.ExecuteNonQuery();
            }
            if (count != 0)
                return true;
            else
                return false;
        }
    }
}